import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib as mpl
import os

import numpy as np
import math
import random
import threading

from datetime import datetime, timedelta
import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import json
import re
import time

import itertools
import warnings
warnings.filterwarnings("ignore")


if __name__ == '__main__':
    try:
        engine = create_engine('postgresql://5ga-cmcc:5ga-cmcc@localhost:5432/postgres')
        Session = sessionmaker(bind=engine)
        query = """
                    SELECT * FROM traffic_table where port_name = 'xgei-0/2/0/24' ORDER BY time desc LIMIT 200;
                """
        with Session() as session:
            result = session.execute(text(query))
            data = pd.DataFrame(result.fetchall(), columns=result.keys())
            data['time'] = pd.to_datetime(data['time'], format='%Y-%m-%d %H%M%S')
            data.sort_values('time', inplace=True)
            data.reset_index(level=None, drop=True, inplace=True)
            session.close()
        # 转换结果为列表

        data['rx_bytes'] = data['rx_bytes'].diff(1)
        data['tx_bytes'] = data['tx_bytes'].diff(1)

        # data.index = pd.DatetimeIndex(data['time'])
        # data = data.resample('10s',closed='right').max()

        data.dropna(axis=0, how='any', inplace=True)

    except Exception as e:
        print(e)

    # 边缘图
    import scipy.stats as stats
    df = data.loc[:, ['time', 'rx_bytes']]
    df.columns = ['time', 'data']

    fig = plt.figure(figsize=(8, 6), dpi=80)
    grid = plt.GridSpec(4, 4, hspace=0.5, wspace=0.2)

    ax_main = fig.add_subplot(grid[:-1, :-1], xticklabels=[])
    ax_right = fig.add_subplot(grid[:-1, -1], xticklabels=[], yticklabels=[])

    ax_main.scatter('time', 'data',  alpha=.9, data=df)
    sns.boxplot(df.data, ax=ax_right, orient="v")

    print(df.describe())

    # 正态分布
    for i in ['rx_bytes', 'tx_bytes']:
        print(i)
        X = data[i].astype(float)
        plt.figure(figsize=(18, 8), dpi=200)
        plt.title(i+'直方图', color='dimgray')
        plt.hist(X, bins=50)
        plt.show()

        plt.figure(figsize=(18, 8), dpi=200)
        plt.title(i+'概率密度图', color='dimgray')
        sns.kdeplot(X, kernel='gau', color="g", alpha=.7)
        plt.show()

        print(stats.skew(X))  # 计算偏度
        print(stats.kurtosis(X))  # 计算峰度

    df = data.loc[:, ['tx_bytes', 'rx_bytes']]


    # 相关性
    ts = data.loc[:, ['rx_bytes']]
    ts.plot(kind="line")  # 默认绘制折线图

    ts = data.loc[:, ['tx_bytes']]
    ts.plot(kind="line")  # 默认绘制折线图

    ts = data.loc[:, ['tx_bytes', 'rx_bytes']]
    ts.plot(kind="line")  # 默认绘制折线图

    # 协方差：绝对值越大，线性关系越强
    data['tx_bytes'].cov(data['rx_bytes'])
    # 相关系数：相关系数在-1到1之间，接近1为正相关，接近-1为负相关，0为不相关。
    data['tx_bytes'].corr(data['rx_bytes'])
